<?php

class GndataController extends Controller
{
	var $maxLevel = 10;

	public function actionIndex() {
		#do nothing for now
		//ini_set("xdebug.max_nesting_level", "0" );
	}

	public function actionGenealogy() {
		$data = array();
        $response['status'] = 'NG';

		if (isset($_POST['member_id'])) {
			$member_id = $_POST['member_id'];
			$response['status'] = 'OK';
			$data['member'] = $this->getMember($member_id); 

			$children = $this->getChildren( $member_id );
	
			$l_downlines = array();
			$r_downlines = array();
			
			if ( isset($children['left']) ) {
			  $data = $children['left'];	
			  array_push( $l_downlines, $data );
			  $this->getDownlines( $data['member_id'], 1, $l_downlines );
			}
			
			if ( isset($children['right']) ) {
			  $data = $children['right'];	
			  array_push( $r_downlines, $data );
			  $this->getDownlines( $data['member_id'], 1, $r_downlines );
			}

		    $l_count = count($l_downlines);
			$r_count = count($r_downlines);

			$response['member']['downlines']['left']['members_count'] = $l_count;
			$response['member']['downlines']['left']['data'] = $l_downlines;
			$response['member']['downlines']['right']['members_count'] = $r_count;
			$response['member']['downlines']['right']['data'] = $r_downlines;

		} else {
			$response['message'] = "Invalid member_id";
		}

        echo json_encode($response);
	}


	public function actionVbMonth () {
		$data = array();
        $response['status'] = 'NG';

		if (isset($_POST['member_id']) && isset($_POST['year']) && isset($_POST['month_no'])) {
			$member_id = $_POST['member_id'];
			$response['status'] = 'OK';
			$response['member'] = $this->getMember($member_id); 

			$response['member']['bv_personal'] = $this->getMemberPersonalBV( $member_id, $_POST['year'], $_POST['month_no']);	
			$children = $this->getChildren($member_id );
	
			$l_downlines = array();
			$r_downlines = array();
			$total_l_points = 0;
			$total_r_points = 0;
			
			if ( isset($children['left']) ) {
			  $data = $children['left'];
			  $bv_personal = $this->getMemberPersonalBV( $data['member_id'], $_POST['year'], $_POST['month_no']);		
			  
			  if ( !$bv_personal )
			      $bv_personal = 0;
			  else
				  $total_l_points = $bv_personal;
			  
		      $data['bv_personal'] = $bv_personal;
			  array_push( $l_downlines, $data );
			  
			  $this->getVolumeDownlines( $data['member_id'], $_POST['year'], $_POST['month_no'], 1, $l_downlines, $total_l_points );
			}
			
			if ( isset($children['right']) ) {
			  $data = $children['right'];	
			  $bv_personal = $this->getMemberPersonalBV( $data['member_id'], $_POST['year'], $_POST['month_no']);		
			  
			  if ( !$bv_personal )
		        $bv_personal = 0;
		      else
		        $total_r_points = $bv_personal;
		    
		      $data['bv_personal'] = $bv_personal;
		      array_push( $r_downlines, $data );

			  $this->getVolumeDownlines( $data['member_id'], $_POST['year'], $_POST['month_no'], 1, $r_downlines, $total_r_points );
			}

		    $l_count = count($l_downlines);
			$r_count = count($r_downlines);

			$response['member']['downlines']['left']['bv_total'] = $total_l_points;
			$response['member']['downlines']['left']['members_count'] = $l_count;
			$response['member']['downlines']['left']['data'] = $l_downlines;
			$response['member']['downlines']['right']['bv_total'] = $total_r_points;
			$response['member']['downlines']['right']['members_count'] = $r_count;
			$response['member']['downlines']['right']['data'] = $r_downlines;

		} else {
			$response['message'] = "Invalid member_id";
		}

        echo json_encode($response);
	}

	public function actionVbTotal () {
		$data = array();
        $response['status'] = 'NG';

		if (isset($_POST['member_id'])) {
			$member_id = $_POST['member_id'];
			$response['status'] = 'OK';
			$response['member'] = $this->getMember($member_id); 

			$response['member']['bv_personal'] = $this->getMemberTotalBV( $member_id );	
			$children = $this->getChildren($member_id );
	
			$l_downlines = array();
			$r_downlines = array();
			$total_l_points = 0;
			$total_r_points = 0;
			
			if ( isset($children['left']) ) {
			  $data = $children['left'];
			  $bv_personal = $this->getMemberTotalBV( $data['member_id']);		
			  
			  if ( !$bv_personal )
			      $bv_personal = 0;
			  else
				  $total_l_points = $bv_personal;
			  
		      $data['bv_personal'] = $bv_personal;
			  array_push( $l_downlines, $data );
			  
			  $this->getTotalVolumeDownlines( $data['member_id'],  1, $l_downlines, $total_l_points );
			}
			
			if ( isset($children['right']) ) {
			  $data = $children['right'];	
			  $bv_personal = $this->getMemberTotalBV( $data['member_id'] );		
			  
			  if ( !$bv_personal )
		        $bv_personal = 0;
		      else
		        $total_r_points = $bv_personal;
		    
		      $data['bv_personal'] = $bv_personal;
		      array_push( $r_downlines, $data );

			  $this->getTotalVolumeDownlines( $data['member_id'], 1, $r_downlines, $total_r_points );
			}

		    $l_count = count($l_downlines);
			$r_count = count($r_downlines);

			$response['member']['downlines']['left']['bv_total'] = $total_l_points;
			$response['member']['downlines']['left']['members_count'] = $l_count;
			$response['member']['downlines']['left']['data'] = $l_downlines;
			$response['member']['downlines']['right']['bv_total'] = $total_r_points;
			$response['member']['downlines']['right']['members_count'] = $r_count;
			$response['member']['downlines']['right']['data'] = $r_downlines;

		} else {
			$response['message'] = "Invalid member_id";
		}

        echo json_encode($response);
	}

	public function getMemberTotalBV($member_id=0 )
	{
		$connection = Yii::app()->db;
		
		$sql = "SELECT SUM(bv_personal) AS bv_total 
		          FROM gn_payouts  
		         WHERE member_id=$member_id
		      GROUP BY member_id";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    if ( isset($data['bv_total']) )
	       return $data['bv_total'];
        else
           return 0;
	}

	public function getTotalVolumeDownlines( $parent_id, $level, &$downlines, &$total_points ) 
	{
		$level++;
		if ( $level >= $this->maxLevel )
			 return;

		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   $bv_total = $this->getMemberTotalBV( $row['member_id'] );
		   
		   if ( $bv_total )
		   {
		   	  $row['bv_total'] = $bv_total;
			  $total_points += $bv_total;
		   	  array_push( $downlines, $row );
		   }
		   
		   $this->getTotalVolumeDownlines( $row['member_id'], $level, $downlines, $total_points );
		}
	}


	public function getChildren( $member_id ) 
	{
		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$member_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		$members = array();
		
		foreach( $dataReader as $row ) {
		   $members[$row['position']] = $row;
		}
		
		return $members;
	}

    private function getMember( $member_id ) 
	{
		$connection = Yii::app()->db;
   
		$sql = "SELECT member_id, first_name, last_name, middle_name,
		               main_address, email, mobile_no, sponsor_id  
		          FROM gn_members 
				 WHERE member_id=$member_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		$data = NULL;
		
		foreach( $dataReader as $row ) { 
		   $data = $row; 
        }
		
		return $data;
    }

	private function getDownlines( $parent_id, $level, &$downlines ) 
	{
		$level++;
		if ( $level >= $this->maxLevel )
			 return;

		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   array_push( $downlines, $row );
		   $this->getDownlines( $row['member_id'], $level, $downlines );
		}
	}

	public function getMemberPersonalBV($member_id=0, $year=0, $month_no=0 )
	{
		$connection = Yii::app()->db;
		
		$sql = "SELECT bv_personal 
		          FROM gn_payouts  
		         WHERE member_id=$member_id
		           AND month_no=$month_no
		           AND year=$year";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    if ( isset($data['bv_personal']) )
	       return $data['bv_personal'];
        else
           return 0;
	}

	public function getVolumeDownlines( $parent_id, $year, $month_no, $level, &$downlines, &$total_points ) 
	{
		$level++;
		if ( $level >= $this->maxLevel )
			 return;

		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   $bv_personal = $this->getMemberPersonalBV( $row['member_id'], $year, $month_no);
		   
		   if ( $bv_personal )
		   {
		   	  $row['bv_personal'] = $bv_personal;
			  $total_points += $bv_personal;
		   	  array_push( $downlines, $row );
		   }
		   
		   $this->getVolumeDownlines( $row['member_id'], $year, $month_no, $level, $downlines, $total_points );
		}
	}
}
